﻿Option Explicit On

Imports System.Text


Public Class PrivateIndividual

    Public Structure PersonInfo
        Public PersonId As Long
        Public Surname As String
        Public Firstname As String
        Public Title As String
        Public JobTitle As String
        Public Qualification As String
        Public Salary As String
        Public DOB As Date
        Public PassportNo As String
        Public PassportExp As Date
        Public DestinationId As Integer
        Public DestinationName As String
        Public NationalityId As Integer
        Public NationalityName As String
        Public ResidenceId As Integer
        Public ResidenceName As String
        Public UKVisaId As Integer
        Public UKVisaName As String
        Public UKVisaExpiry As Date
        Public AccountManagerId As Integer
        Public WhereHeard As String
        Public CompanyRoleId As Integer
        Public CompanyId As Integer
        Public WebUserName As String
        Public WebPassword As String
        Public WebAccess As Integer
        Public SkilledOccupationId As Integer
        Public SkillOccupationName As String
        Public EnableLogin As String
    End Structure

    Public Structure AddressInfo
        Public AddressId As Long
        Public AddressLinkId As Long
        Public AddressName As String
        Public AddressText As String
        Public Region As String
        Public Postcode As String
        Public CountryId As Integer
        Public CountryName As String
        Public AddressType As AddressTypes
        Public LinkId As Long 'Either person id or company id
    End Structure

    Public Structure ParentInfo
        Public ParentId As Long
        Public ParentName As String
    End Structure

    Public Enum PhoneTypes
        PhoneOther = 0
        PhoneMain = 1
        PhoneFax = 2
        PhoneBoth = 3
    End Enum

    Public Enum AddressTypes
        NotSet = 0
        Person = 1
        Company = 2
    End Enum

    Public Enum AddressLinkTypes
        NotSet = 0
        Correspondence = 1
        Invoice = 2
        Both = 3
    End Enum


    Public Shared Function AddAdvertising(ByVal WhereHeard As String, ByVal ConName As String, ByVal PersonId As Long) As Long



        'Every time a person is created we ask them where they heard about us
        Dim cmn As New CommonFuncs
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("INSERT INTO D_Advertising (Advertising_WhereHear, Advertising_EnteredBy, Advertising_LinkID, Advertising_LinkType)")
                .Append(" VALUES ('" & cmn.SwapQuotes(WhereHeard) & "','" & cmn.SwapQuotes(ConName) & "'," & PersonId & ",0)")
            End With

            If dataAccess.ExecuteSQL(SQL.ToString) Then
                Return dataAccess.GetIdentity
            End If
        Catch ex As Exception
            Return 0
        Finally

            dataAccess = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function UpdateAdvertising(ByVal LinkId As Long, ByVal WhereHeard As String) As Boolean

        Dim cmn As New CommonFuncs
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("Update D_Advertising Set Advertising_WhereHear = '" & cmn.SwapQuotes(WhereHeard.Trim) & "'")
                .Append(" Where Advertising_LinkType = 0 ")
                .Append(" And Advertising_LinkID = " & LinkId)
            End With

            Return dataAccess.ExecuteSQL(SQL.ToString)
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function AddPerson(ByVal Person As PersonInfo, ByVal OldPerson As PersonInfo) As Long
        Dim cmn As New CommonFuncs
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            If Person.PersonId = 0 Then

                With SQL
                    .Append("INSERT INTO D_Person (Person_FName,Person_EnteredBy,Person_DTEntered, Person_Lname, Person_Title,")
                    .Append("Person_NatID,Person_ResidentID,Person_UKVisaID,Person_DestinationID, Person_Jobtitle, Person_salary, Person_Qualification, Person_PassportNo,")
                    .Append("person_dob,person_expirydt,person_PassportDTexpiry,Person_CompanyID, Person_CompanyRoleID,")
                    .Append("Person_AccountManager) values (")

                    .Append("'" & cmn.SwapQuotes(Person.Firstname) & "',")
                    .Append(GlobalCRM.CurrentConID & ",")
                    .Append("'" & cmn.FormatDate(Now, True) & "',")
                    .Append("'" & cmn.SwapQuotes(Person.Surname) & "',")
                    .Append("'" & cmn.SwapQuotes(Person.Title) & "',")
                    .Append(Person.NationalityId & "," & Person.ResidenceId & "," & Person.UKVisaId & ",")
                    .Append(Person.DestinationId & ",")
                    .Append("'" & cmn.SwapQuotes(Person.JobTitle) & "',")
                    .Append("'" & cmn.SwapQuotes(Person.Salary) & "',")
                    .Append("'" & cmn.SwapQuotes(Person.Qualification) & "',")
                    .Append("'" & cmn.SwapQuotes(Person.PassportNo) & "',")

                    If Person.DOB = "#12:00:00 AM#" Then
                        .Append("null,")
                    ElseIf IsDate(Person.DOB) Then
                        .Append("'" & cmn.FormatDate(Person.DOB) & "',")
                    Else
                        .Append("null,")
                    End If

                    If Person.UKVisaExpiry = "#12:00:00 AM#" Then
                        .Append("null,")
                    ElseIf IsDate(Person.UKVisaExpiry) Then
                        .Append("'" & cmn.FormatDate(Person.UKVisaExpiry) & "',")
                    Else
                        .Append("null,")
                    End If

                    If Person.PassportExp = "#12:00:00 AM#" Then
                        .Append("null,")
                    ElseIf IsDate(Person.PassportExp) Then
                        .Append("'" & cmn.FormatDate(Person.PassportExp) & "',")
                    Else
                        .Append("null,")
                    End If

                    .Append(Person.CompanyId & "," & Person.CompanyRoleId & "," & Person.AccountManagerId & ")")

                End With

                If dataAccess.ExecuteSQL(SQL.ToString) Then
                    'Return Person_Id
                    Return dataAccess.GetIdentity
                End If

            ElseIf Person.PersonId > 0 Then
                'Edit mode
                Dim Changes As New StringBuilder

                With SQL
                    .Append("update d_person set person_fname = '" & cmn.SwapQuotes(Person.Firstname.Trim) & "',")
                    .Append("person_lname = '" & cmn.SwapQuotes(Person.Surname.Trim) & "',")
                    .Append("person_title = '" & cmn.SwapQuotes(Person.Title.Trim) & "',")
                    If Person.DOB = "#12:00:00 AM#" Then
                        .Append("person_dob = " & "null" & ",")
                    Else
                        .Append("person_dob = '" & cmn.FormatDate(Person.DOB) & "',")
                    End If
                    .Append("person_jobtitle = '" & cmn.SwapQuotes(Person.JobTitle) & "',")
                    .Append("person_qualification = '" & cmn.SwapQuotes(Person.Qualification) & "',")
                    .Append("person_salary = '" & cmn.SwapQuotes(Person.Salary) & "',")
                    .Append("person_companyroleid = " & Person.CompanyRoleId & ",")
                    .Append("person_destinationid = " & Person.DestinationId & ",")
                    .Append("person_natid = " & Person.NationalityId & ",")
                    .Append("person_residentid = " & Person.ResidenceId & ",")
                    .Append("person_ukvisaid = " & Person.UKVisaId & ",")
                    If Person.UKVisaExpiry = "#12:00:00 AM#" Then
                        .Append("person_expirydt = " & "null" & ",")
                    Else
                        .Append("person_expirydt = '" & cmn.FormatDate(Person.UKVisaExpiry) & "',")
                    End If
                    .Append("person_passportno = '" & cmn.SwapQuotes(Person.PassportNo) & "',")
                    If Person.PassportExp = "#12:00:00 AM#" Then
                        .Append("person_passportdtexpiry = " & "null" & ",")
                    Else
                        .Append("person_passportdtexpiry = '" & cmn.FormatDate(Person.PassportExp) & "',")
                    End If

                    .Append("person_accountmanager = " & Person.AccountManagerId & ",")
                    .Append("person_skilledoccid = " & Person.SkilledOccupationId & ",")
                    .Append("person_enablelogin = " & Person.EnableLogin)
                    .Append(" where person_id = " & Person.PersonId)
                End With

                With Changes

                    .Append("Details changed for " & OldPerson.Firstname & " " & OldPerson.Surname & vbCrLf & "----------------------------------" & vbCrLf)

                    If Person.Surname <> OldPerson.Surname Then
                        .Append("Surname changed from " & OldPerson.Surname & " to " & Person.Surname & vbCrLf)
                    End If

                    If Person.Firstname <> OldPerson.Firstname Then
                        .Append("First name changed from " & OldPerson.Firstname & " to " & Person.Firstname & vbCrLf)
                    End If

                    If Person.Title <> OldPerson.Title Then
                        .Append("Title changed from " & OldPerson.Title & " to " & Person.Title & vbCrLf)
                    End If

                    If Person.DOB <> OldPerson.DOB Then
                        .Append("DoB changed from " & OldPerson.DOB & " to " & Person.DOB & vbCrLf)
                    End If

                    If Person.AccountManagerId <> OldPerson.AccountManagerId Then
                        .Append("Account manager changed from " & CommonDataFunctions.GetConsultantName(OldPerson.AccountManagerId) & " to " & CommonDataFunctions.GetConsultantName(Person.AccountManagerId) & vbCrLf)
                    End If

                    If Person.UKVisaExpiry <> OldPerson.UKVisaExpiry Then
                        .Append("UK Visa expiry date changed from " & OldPerson.UKVisaExpiry & " to " & Person.UKVisaExpiry & vbCrLf)
                    End If

                    If Person.Salary <> OldPerson.Salary Then
                        .Append("Salary changed from " & OldPerson.Salary & " to " & Person.Salary & vbCrLf)
                    End If

                    If Person.SkilledOccupationId <> OldPerson.SkilledOccupationId Then
                        .Append("Skilled occupation changed from " & OldPerson.SkilledOccupationId & " to " & Person.SkilledOccupationId & vbCrLf)
                    End If

                    If Person.NationalityId <> OldPerson.NationalityId Then
                        .Append("Nationality changed from " & OldPerson.NationalityName & " to " & Person.NationalityName & vbCrLf)
                    End If

                    If Person.ResidenceId <> OldPerson.ResidenceId Then
                        .Append("Residence country changed from " & OldPerson.ResidenceName & " to " & Person.ResidenceName & vbCrLf)
                    End If

                    If Person.DestinationId <> OldPerson.DestinationId Then
                        .Append("Destination country changed from " & OldPerson.DestinationName & " to " & Person.DestinationName & vbCrLf)
                    End If

                    If Person.UKVisaId <> OldPerson.UKVisaId Then
                        .Append("UK Visa status changed from " & OldPerson.UKVisaName & " to " & Person.UKVisaName & vbCrLf)
                    End If

                    If Person.JobTitle <> OldPerson.JobTitle Then
                        .Append("Job title changed from " & OldPerson.JobTitle & " to " & Person.JobTitle & vbCrLf)
                    End If

                    If Person.PassportNo <> OldPerson.PassportNo Then
                        .Append("Passport number changed from " & OldPerson.PassportNo & " to " & Person.PassportNo & vbCrLf)
                    End If

                    If Person.PassportExp <> OldPerson.PassportExp Then
                        .Append("Passport expiry date changed from " & OldPerson.PassportExp & " to " & Person.PassportExp & vbCrLf)
                    End If

                    If Person.Qualification <> OldPerson.Qualification Then
                        .Append("Qualification changed from " & OldPerson.Qualification & " to " & Person.Qualification & vbCrLf)
                    End If

                    If Person.CompanyRoleId <> Person.CompanyRoleId Then
                        .Append("Company role changed from " & OldPerson.CompanyRoleId & " to " & Person.CompanyRoleId & vbCrLf)
                    End If
                    If Person.EnableLogin <> Person.EnableLogin Then
                        .Append("Login Enabling changed from " & OldPerson.EnableLogin & " to " & Person.EnableLogin & vbCrLf)
                    End If

                    If Person.WhereHeard <> OldPerson.WhereHeard Then
                        .Append("Where heard changed from " & OldPerson.WhereHeard & " to " & Person.WhereHeard & vbCrLf)
                    End If

                End With

                If dataAccess.ExecuteSQL(SQL.ToString) Then

                    ' catch people who do not have a Where Hear value
                    If IsNothing(OldPerson.WhereHeard) Then
                        AddAdvertising(Person.WhereHeard, GlobalCRM.CurrentConName, GlobalCRM.CurrentCandidateID)
                    ElseIf Person.WhereHeard <> OldPerson.WhereHeard Then
                        UpdateAdvertising(GlobalCRM.CurrentCandidateID, Person.WhereHeard)
                    End If

                    'If Person.WhereHeard <> OldPerson.WhereHeard Then
                    '    UpdateAdvertising(GlobalCRM.CurrentCandidateID, Person.WhereHeard)
                    'End If

                    tsk.GblFunc_Add_Event("Details Changed", Changes.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCandidateID, TasksEvents.EventTypes.EventPerson)

                    Return Person.PersonId
                End If

            End If
        Catch ex As Exception
            Return 0
        Finally
            tsk = Nothing
            dataAccess = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    'Public Shared Function AddEmailAddress(ByVal EmailAddress As String, ByVal EmailName As String, ByVal ConName As String, ByVal PersonId As Long) As Long

    '    Dim SQL As New StringBuilder

    '    With SQL
    '        .Append("Insert into D_EMailAddress (EMailAddress_Address, EMailAddress_Name, EMailAddress_Type, EMailAddress_EnteredBy, EMailAddress_DTEntered, EMailAddress_PersonID)")
    '        .Append(" Values ('" & cmn.SwapQuotes(EmailAddress.Trim) & "','" & cmn.SwapQuotes(EmailName.Trim) & "',1,'")
    '        .Append(cmn.SwapQuotes(ConName.Trim) & "','" & FormatDate(Now, True) & "'," & PersonId & ")")
    '    End With

    '    If dataAccess.ExecuteSQL(SQL.ToString) Then
    '        'Email ID
    '        Return dataAccess.GetIdentity
    '    End If

    'End Function

    Public Shared Function DeletePhoneNumber(ByVal PhoneId As Long, Optional ByVal PersonId As Long = 0, Optional ByVal PhoneName As String = "", Optional ByVal PhoneNumber As String = "") As Boolean
        Dim cmn As New CommonFuncs
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder



            With SQL
                .Append("delete from d_phonenumber where phonenumber_id = " & PhoneId)
            End With

            DeletePhoneNumber = dataAccess.ExecuteSQL(SQL.ToString)

            If (PhoneName <> "" Or PhoneNumber <> "") And PersonId > 0 Then

                Dim Msg As New StringBuilder

                Msg.Append("Phone number deleted" & vbCrLf & vbCrLf & PhoneName.Trim & vbCrLf & PhoneNumber.Trim)
                tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)

            End If
            Return True
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
            cmn = Nothing
            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function DeleteEmailAddress(ByVal EmailId As Long, Optional ByVal PersonId As Long = 0, Optional ByVal EmailName As String = "", Optional ByVal EmailAddress As String = "") As Boolean

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder



            SQL.Append("DELETE FROM D_EMailAddress WHERE EMailAddress_ID = " & EmailId)

            DeleteEmailAddress = dataAccess.ExecuteSQL(SQL.ToString)

            If (EmailName <> "" Or EmailAddress <> "") And EmailId > 0 Then

                Dim Msg As New StringBuilder

                Msg.Append("Email address deleted" & vbCrLf & vbCrLf & EmailName.Trim & vbCrLf & EmailAddress.Trim)
                tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
            End If
            Return True
        Catch ex As Exception
            Return False
        Finally

            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function AddPhoneNumber(ByVal PhoneNumber As String, ByVal PhoneName As String, ByVal ConName As String, ByVal PersonId As Long, ByVal PhoneType As PhoneTypes, Optional ByVal PhoneId As Long = 0, Optional ByVal OldPhoneName As String = "", Optional ByVal OldPhoneNumber As String = "") As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder

        Dim Msg As StringBuilder
        Try

            SQL = New StringBuilder
            Msg = New StringBuilder
            Dim NewPhoneId As Long

            If PhoneId = 0 Then

                'New phone number

                'See if person has any other numbers, if not set it as the main number

                SQL.Append("select count(*) from d_phonenumber where phonenumber_personid = " & PersonId)
                If dataAccess.GetSQLScalar(SQL.ToString) = 0 Then
                    PhoneType = PhoneTypes.PhoneMain
                End If

                SQL = New StringBuilder

                With SQL
                    .Append("Insert into D_PhoneNumber (Phonenumber_number, Phonenumber_Name, Phonenumber_Type, Phonenumber_EnteredBy, Phonenumber_DTEntered, Phonenumber_PersonID)")
                    .Append(" Values('" & cmn.SwapQuotes(PhoneNumber.Trim) & "','" & cmn.SwapQuotes(PhoneName.Trim) & "'," & PhoneType & ",")
                    .Append("'" & cmn.SwapQuotes(ConName) & "','" & cmn.FormatDate(Now, True) & "'," & PersonId & ")")
                End With

                Msg.Append("New phone number added." & vbCrLf & vbCrLf & PhoneName.Trim & vbCrLf & PhoneNumber.Trim)

            Else
                'Edit existing number
                With SQL
                    .Append("update d_phonenumber set Phonenumber_number = '" & cmn.SwapQuotes(PhoneNumber.Trim) & "',")
                    .Append("phonenumber_name = '" & PhoneName.Trim & "' ")
                    .Append("where phonenumber_id = " & PhoneId)
                End With

                With Msg
                    .Append("Phone number changed." & vbCrLf & vbCrLf)

                    If PhoneName <> OldPhoneName Then
                        .Append("'" & cmn.SwapQuotes(OldPhoneName.Trim) & "' to '" & cmn.SwapQuotes(PhoneName.Trim) & "'" & vbCrLf)
                    End If

                    If PhoneNumber <> OldPhoneNumber Then
                        .Append("'" & cmn.SwapQuotes(OldPhoneNumber.Trim) & "' to '" & cmn.SwapQuotes(PhoneNumber.Trim) & "'")
                    End If
                End With

            End If

            If dataAccess.ExecuteSQL(SQL.ToString) Then
                'Return Phone ID
                If PhoneId = 0 Then
                    NewPhoneId = dataAccess.GetIdentity
                Else
                    NewPhoneId = PhoneId
                End If
                tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
                Return NewPhoneId
            End If
        Catch ex As Exception
            Return 0
        Finally
            cmn = Nothing
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function AddDependant(ByVal ParentPersonId As Long, ByVal PersonId As Long, ByVal ParentDescription As String, ByVal ChildDescription As String, ByVal ConName As String, ByVal PublicRelationship As Boolean) As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs

        Try

            SQL = New StringBuilder
            With SQL
                .Append("Insert into D_Dependants (Dependant_ParentID, Dependant_ChildID, Dependant_Parentdesc, Dependant_Childdesc, Dependant_Enteredby, Dependant_Public)")
                .Append(" Values(" & ParentPersonId & ", " & PersonId & ", '" & cmn.SwapQuotes(ParentDescription.Trim) & "',")
                .Append("'" & cmn.SwapQuotes(ChildDescription.Trim) & "','" & ConName & "',")
                .Append(IIf(PublicRelationship, 1, 0) & ")")
            End With

            If dataAccess.ExecuteSQL(SQL.ToString) Then
                'Return Dependent ID
                Return dataAccess.GetIdentity
            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function SetMainOrFaxPhoneNumber(ByVal PhoneId As Long, ByVal ChangeToPhoneType As PhoneTypes, ByVal PersonId As Long, ByVal PhoneName As String, ByVal PhoneNumber As String) As Boolean

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs

        Try

            SQL = New StringBuilder
            Dim Msg As New StringBuilder

            Select Case ChangeToPhoneType
                Case PhoneTypes.PhoneBoth
                    'Phone is already set as fax, so set to Both

                    'Set any existing main number to zero
                    SQL = New StringBuilder

                    SQL.Append("Update D_PhoneNumber set Phonenumber_Type = 0 where Phonenumber_PersonID = " & PersonId & " AND Phonenumber_Type in (" & PhoneTypes.PhoneMain & "," & PhoneTypes.PhoneFax & ")")

                    If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                        Exit Function
                    End If

                    SQL = New StringBuilder

                    'Set this number to be both (3)
                    SQL.Append("Update D_PhoneNumber set Phonenumber_Type = " & PhoneTypes.PhoneBoth & " where Phonenumber_ID = " & PhoneId)

                    If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                        Exit Function
                    End If

                    Msg.Append("Phone number '" & PhoneName.Trim & "' (" & PhoneNumber.Trim & ") set as both main and fax number.")


                Case PhoneTypes.PhoneMain, PhoneTypes.PhoneFax
                    'Is nothing so set to Main

                    'Set existing numbers to 0 or 2, depending on what they are now. (using case update saves doing two updates)

                    SQL = New StringBuilder

                    With SQL
                        .Append("UPDATE D_PhoneNumber ")
                        .Append(" SET Phonenumber_type = ")
                        If ChangeToPhoneType = PhoneTypes.PhoneMain Then
                            .Append(" CASE phonenumber_type WHEN 3 THEN 2 WHEN 1 THEN 0 END")
                            .Append(" WHERE Phonenumber_PersonID = " & PersonId)
                            .Append(" AND Phonenumber_type in (1,3)")
                        ElseIf ChangeToPhoneType = PhoneTypes.PhoneFax Then
                            .Append(" CASE phonenumber_type WHEN 3 THEN 1 WHEN 2 THEN 0 END")
                            .Append(" WHERE Phonenumber_PersonID = " & PersonId)
                            .Append(" AND Phonenumber_type in (2,3)")
                        End If
                    End With

                    If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                        Exit Function
                    End If

                    SQL = New StringBuilder

                    'Set this number to main / fax
                    SQL.Append("Update D_PhoneNumber set Phonenumber_Type = " & ChangeToPhoneType & " where Phonenumber_ID = " & PhoneId)

                    If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                        Exit Function
                    End If

                    If ChangeToPhoneType = PhoneTypes.PhoneMain Then
                        Msg.Append("Phone number '" & PhoneName.Trim & "' (" & PhoneNumber.Trim & ") set as main number.")
                    ElseIf ChangeToPhoneType = PhoneTypes.PhoneFax Then
                        Msg.Append("Phone number '" & PhoneName.Trim & "' (" & PhoneNumber.Trim & ") set as fax number.")
                    End If

            End Select

            tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)

            Return True
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try

    End Function

    Public Shared Function AddEmailAddress(ByVal EmailTitle As String, ByVal EmailAddress As String, ByVal PersonId As Long, ByVal MainEmail As Boolean, ByVal ConName As String, Optional ByVal EmailId As Long = 0, Optional ByVal OldEmailTitle As String = "", Optional ByVal OldEmailAddress As String = "") As Long

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Dim msg As StringBuilder

        Try

            SQL = New StringBuilder

            msg = New StringBuilder
        Dim NewEmailId As Long

        If EmailId = 0 Then
            'Adding a new address

            'Check if this is the first adddress, if so it is automaticaly the Main address

            SQL = New StringBuilder

            SQL.Append("select count(*) from d_emailaddress where emailaddress_personid = " & PersonId)

            If dataAccess.GetSQLScalar(SQL.ToString) = 0 Then
                MainEmail = True
            End If

            SQL = New StringBuilder

            With SQL
                .Append("Insert into D_EMailAddress (EMailaddress_Name, EMailAddress_DTEntered, EMailAddress_Enteredby, EMailAddress_Address, EMailAddress_PersonID, EMailAddress_Type)")
                    .Append(" Values('" & cmn.SwapQuotes(EmailTitle.Trim) & "', '" & cmn.FormatDate(Now, True) & "',")
                .Append("'" & cmn.SwapQuotes(ConName) & "', '" & cmn.SwapQuotes(EmailAddress) & "', " & PersonId & "," & IIf(MainEmail, 1, 0) & ")")
            End With

            Msg.Append("E-Mail address added" & vbCrLf & vbCrLf & EmailTitle.Trim & vbCrLf & EmailAddress.Trim)
        Else

            'Updating existing email address
            SQL = New StringBuilder

            With SQL
                .Append("Update D_EmailAddress set EMailAddress_Name = '" & EmailTitle.Trim & "',")
                .Append("EMailaddress_Address = '" & cmn.SwapQuotes(EmailAddress.Trim) & "'")
                .Append("where EMailaddress_ID = " & EmailId)
            End With

            With Msg
                .Append("E-Mail address changed. " & vbCrLf & vbCrLf)

                If EmailTitle <> OldEmailTitle Then
                    .Append(OldEmailTitle & " to " & EmailTitle & vbCrLf)
                End If

                If EmailAddress <> OldEmailAddress Then
                    .Append(OldEmailAddress & " to " & EmailAddress)
                End If
            End With

        End If

        If dataAccess.ExecuteSQL(SQL.ToString) Then
            If EmailId = 0 Then
                NewEmailId = dataAccess.GetIdentity
            Else
                NewEmailId = EmailId
            End If
                tsk.GblFunc_Add_Event("Details Changed", msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
            Return NewEmailId
        End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function SetMainEmailAddress(ByVal EmailId As Long, ByVal PersonId As Long, ByVal EmailName As String, ByVal EmailAddress As String) As Boolean


        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Try
            SQL = New StringBuilder

            With SQL
                .Append("UPDATE D_EMailaddress SET EMailaddress_Type = ")
                .Append(" CASE EmailAddress_ID WHEN " & EmailId & " THEN 1 ELSE 0 END ")
                .Append(" WHERE Emailaddress_PersonID = " & PersonId)
            End With

            If dataAccess.ExecuteSQL(SQL.ToString) Then

                Dim Msg As New StringBuilder

                Msg.Append("Email address " & EmailName.Trim & " (" & EmailAddress.Trim & ") set as main address")

                tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)

                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function AddAddress(ByVal Address As String, ByVal CountyOrRegion As String, ByVal Country As String, ByVal Postcode As String, ByVal ConName As String, ByVal AddressType As AddressTypes, ByVal AddressName As String, Optional ByVal LinkId As Long = 0, Optional ByVal AddressLinkId As Long = 0, Optional ByVal AddressId As Long = 0, Optional ByVal OldAddressName As String = "", Optional ByVal OldAddress As String = "", Optional ByVal OldPostcode As String = "", Optional ByVal OldCountry As String = "", Optional ByVal OldRegion As String = "") As Long

        'Add the address to the main address table

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Try
            SQL = New StringBuilder
            'Dim AddressId As Long
            Dim LinkCol As String

            If AddressLinkId = 0 Then

                'New address record

                With SQL
                    .Append("INSERT INTO D_Address ( Address_Text, Address_EnteredBy, Address_DTEntered, Address_Postcode, Address_Region, Address_Country )")
                    .Append(" VALUES ( '" & cmn.SwapQuotes(Address.Trim) & "', '" & cmn.SwapQuotes(ConName.Trim) & "', '" & cmn.FormatDate(Now, True) & "', '" & cmn.SwapQuotes(Postcode.Trim) & "',")
                    .Append("'" & cmn.SwapQuotes(CountyOrRegion.Trim) & "',")
                    .Append("'" & Country.Trim & "')")
                End With

                If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If

                AddressId = dataAccess.GetIdentity

                'Add the link to the company/person

                If AddressType = AddressTypes.Company Then
                    LinkCol = "AddressLink_CompanyID"
                Else
                    LinkCol = "AddressLink_PersonID"
                End If

                SQL = New StringBuilder

                With SQL
                    .Append("INSERT INTO D_AddressLink ( AddressLink_AddressID, AddressLink_EnteredBy, AddressLink_DTEntered, ")
                    .Append(LinkCol & ", AddressLink_Name, AddressLink_Owner )")
                    .Append(" VALUES (" & AddressId & ", '" & cmn.SwapQuotes(ConName.Trim) & "', '" & cmn.FormatDate(Now, True) & "',")
                    .Append(LinkId & ",'" & cmn.SwapQuotes(AddressName.Trim) & "',1)")
                End With

                If dataAccess.ExecuteSQL(SQL.ToString) Then
                    Return AddressId
                End If

            Else
                'Update existing record
                Dim NameChanged, Dirty As Boolean
                Dim Msg As New StringBuilder
                Dim OldString As String

                If AddressName <> OldAddressName Then
                    NameChanged = True
                    Msg.Append("Address name changed from " & OldAddressName.Trim & " to " & AddressName.Trim & Chr(13) & Chr(10))
                End If

                SQL.Append("Update D_Address Set ")
                If StrComp(Address, OldAddress) <> 0 Then
                    Dirty = True

                    If OldAddress.Trim = String.Empty Then
                        OldString = "Blank"
                    Else
                        OldString = vbCrLf & "----------" & vbCrLf & OldAddress.Trim & vbCrLf & "----------" & vbCrLf
                    End If

                    SQL.Append(" Address_text = '" & cmn.SwapQuotes(Address.Trim) & "', ")
                    Msg.Append("Address changed from " & OldString & " to " & vbCrLf & "----------" & vbCrLf & cmn.SwapQuotes(Address.Trim) & vbCrLf & "----------" & vbCrLf)
                End If


                If Postcode.Trim <> OldPostcode.Trim Then

                    If Postcode.Trim = String.Empty Then
                        OldString = "blank"
                    Else
                        OldString = OldPostcode.Trim
                    End If

                    Dirty = True
                    SQL.Append(" Address_postcode = '" & cmn.SwapQuotes(Postcode.Trim) & "', ")
                    Msg.Append("Postcode changed from " & OldString & " to " & cmn.SwapQuotes(Postcode.Trim) & vbCrLf)
                End If

                If Country.Trim <> OldCountry.Trim Then

                    If OldCountry.Trim = String.Empty Then
                        OldString = "blank"
                    Else
                        OldString = OldCountry.Trim
                    End If

                    Dirty = True
                    SQL.Append(" Address_country = '" & cmn.SwapQuotes(Country.Trim) & "', ")
                    Msg.Append("Country changed from " & OldString & " to " & cmn.SwapQuotes(Country.Trim) & vbCrLf)
                End If

                If CountyOrRegion.Trim <> OldRegion Then
                    If OldRegion.Trim = String.Empty Then
                        OldString = "blank"
                    Else
                        OldString = OldRegion.Trim
                    End If

                    Dirty = True

                    SQL.Append(" Address_Region = '" & cmn.SwapQuotes(CountyOrRegion.Trim) & "', ")
                    Msg.Append("County changed from " & OldString & " to " & cmn.SwapQuotes(CountyOrRegion.Trim) & vbCrLf)
                End If


                If Not Dirty And Not NameChanged Then
                    'Nothing changed
                    Return AddressLinkId
                    Exit Function
                End If

                If Dirty Then
                    SQL.Remove(SQL.Length - 2, 2)
                    SQL.Append(" Where Address_ID = " & AddressId)
                    If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                        Exit Function
                    End If
                End If

                If NameChanged Then
                    SQL = New StringBuilder
                    With SQL
                        .Append("Update D_AddressLink set Addresslink_Name = '" & cmn.SwapQuotes(AddressName.Trim) & "' ")
                        .Append("where AddressLink_ID = " & AddressLinkId)
                    End With

                    If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                        Exit Function
                    End If
                End If

                If AddressType = AddressTypes.Company Then
                    Msg.Append("Company address details, " & OldAddressName.Trim & ", changed as below " & vbCrLf & "-------------------" & vbCrLf & Msg.ToString)
                    tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCompanyID, TasksEvents.LinkTypes.Company)
                ElseIf AddressType = AddressTypes.Person Then
                    Msg.Append("Personal address details for " & GlobalCRM.CurrentCandidateName.Trim & ", " & OldAddressName.Trim & ", changed as below " & vbCrLf & "-------------------" & vbCrLf & Msg.ToString)
                    tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCandidateID, TasksEvents.LinkTypes.Person)
                End If

                Return AddressLinkId

            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function GetAddress(ByVal AddressLinkId As Long) As AddressInfo

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Try
            SQL = New StringBuilder
            Dim myData As DataTable
            Dim myRow As DataRow

            GetAddress = Nothing

            With SQL
                .Append("select a.address_id, a.address_text,a.address_postcode,a.address_region,a.address_country,lnk.addresslink_name ")
                .Append("from d_addresslink lnk, d_address a ")
                .Append("where lnk.addresslink_addressid = a.address_id ")
                .Append("and lnk.addresslink_id = " & AddressLinkId)
            End With

            myData = dataAccess.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    myRow = myData.Rows(0)

                    Dim myAddress As New AddressInfo

                    With myAddress
                        .AddressId = myRow.Item("address_id")
                        .AddressText = myRow.Item("address_text")
                        .Postcode = myRow.Item("address_postcode")
                        .Region = myRow.Item("address_region")
                        .CountryName = myRow.Item("address_country")
                        .AddressName = myRow.Item("addresslink_name")
                    End With

                    Return myAddress

                End If
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function GetAddressIdFromAddressLink(ByVal AddressLinkId As Long) As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try

            Return dataAccess.GetSQLScalar("select addresslink_addressid from d_addresslink where addresslink_id = " & AddressLinkId)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try
    End Function

    Public Shared Function DeleteAddress(ByVal AddressLinkId As Long, ByVal AddressType As AddressTypes, Optional ByVal PersonName As String = "") As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim Msg As StringBuilder

        Try
            SQL = New StringBuilder

            Dim AddressId As Long = GetAddressIdFromAddressLink(AddressLinkId)
            Dim OldAddress As AddressInfo = GetAddress(AddressLinkId)

            Msg = New StringBuilder

            SQL.Append("Select addresslink_type from D_Addresslink where Addresslink_AddressID = " & AddressId)

            If CType(dataAccess.GetSQLScalar(SQL.ToString), AddressLinkTypes) <> AddressLinkTypes.NotSet Then
                MsgBox("This address is in use as either a correspondence or invoice address, it cannot be deleted until these functions are moved to a different address", MsgBoxStyle.Exclamation)
                Exit Function
            End If

            If MsgBox("Are you sure you want to delete this address?", vbYesNo + vbExclamation, "Confirm delete") <> MsgBoxResult.Yes Then
                Exit Function
            End If

            With Msg

                If AddressType = AddressTypes.Company Then
                    .Append("Company Address deleted (removed details below)")
                Else
                    .Append("Address for " & PersonName.Trim & " deleted (removed details below)")
                End If

                .Append(vbCrLf & "Name: " & OldAddress.AddressName.Trim)
                .Append(OldAddress.AddressText.Trim)

                If OldAddress.Region.Trim <> "" Then
                    .Append(vbCrLf & OldAddress.Region.Trim)
                End If

                If OldAddress.Postcode.Trim <> "" Then
                    .Append(vbCrLf & OldAddress.Postcode.Trim)
                End If

                If OldAddress.CountryName.Trim <> "" Then
                    .Append(vbCrLf & OldAddress.CountryName.Trim)
                End If

            End With

            SQL = New StringBuilder
            SQL.Append("Delete from D_address where address_ID = " & AddressId)

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            SQL = New StringBuilder
            SQL.Append("Delete from D_addresslink where addresslink_addressID = " & AddressLinkId)

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            If AddressType = AddressTypes.Company Then
                tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCompanyID, TasksEvents.LinkTypes.Company)
            ElseIf AddressType = AddressTypes.Person Then
                tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCandidateID, TasksEvents.LinkTypes.Person)
            End If

            Return True
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
            tsk = Nothing
            SQL = Nothing
            Msg = Nothing
        End Try
    End Function

    Public Shared Function SetCorrespOrInvAddress(ByVal AddressLinkId As Long, ByVal PersonId As Long, ByVal AddressName As String, ByVal ChangeTo As AddressLinkTypes) As Boolean

        'Flags an address as being used for correspondence and / or invoices
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Dim Msg As StringBuilder

        Try
            SQL = New StringBuilder
            Msg = New StringBuilder


            'Set any existing 'both' value back to 'Inv' and main value to nothing
            With SQL
                Select Case ChangeTo
                    Case AddressLinkTypes.Both
                        .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 2 then 0 when 1 then 0 when 3 then 0 end ")
                        .Append("WHERE AddressLink_PersonID = " & PersonId & " AND AddressLink_Type in (1,2)")
                    Case AddressLinkTypes.Correspondence
                        .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 1 then 0 when 3 then 2 end ")
                        .Append("WHERE AddressLink_PersonID = " & PersonId & " AND AddressLink_Type in (1,3)")
                    Case AddressLinkTypes.Invoice
                        .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 2 then 0 when 3 then 1 end ")
                        .Append("WHERE AddressLink_PersonID = " & PersonId & " AND AddressLink_Type in (2,3)")
                End Select
            End With

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            'Set selected address to Corr, Inv or Both
            If ChangeTo = AddressLinkTypes.Correspondence Then Msg.Append(AddressName.Trim & " set as correspondence address")
            If ChangeTo = AddressLinkTypes.Invoice Then Msg.Append(AddressName.Trim & " set as invoice address")
            If ChangeTo = AddressLinkTypes.Both Then Msg.Append(AddressName.Trim & " set as both correspondence and invoice address")

            SQL = New StringBuilder
            SQL.Append("Update D_AddressLink SET AddressLink_Type = " & ChangeTo & " WHERE AddressLink_ID = " & AddressLinkId)

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            tsk.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)

            Return True
        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
            tsk = Nothing
            SQL = Nothing
            Msg = Nothing
        End Try
    End Function

    Public Shared Function SetAddressAsCompany(ByVal AddressLinkId As Long, ByVal ConName As String, ByVal CompanyId As Long, ByVal AddressName As String) As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Try
            Dim AddressId As Long = GetAddressIdFromAddressLink(AddressLinkId)
            Dim SQL As New StringBuilder

            If AddressId = 0 Then
                Exit Function
            End If

            With SQL
                .Append("INSERT INTO D_AddressLink ( AddressLink_AddressID, AddressLink_EnteredBy, AddressLink_DTEntered, AddressLink_CompanyID, AddressLink_Name, AddressLink_Owner )")
                .Append(" VALUES (" & AddressId & ", '" & cmn.SwapQuotes(ConName.Trim) & "', getdate(), " & CompanyId & ",'" & cmn.SwapQuotes(AddressName.Trim) & "', 1)")
            End With

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            SQL = New StringBuilder

            With SQL
                .Append("UPDATE D_AddressLink SET AddressLink_Owner = 0")
                .Append(" WHERE AddressLink_ID = " & AddressLinkId)
            End With

            If Not dataAccess.ExecuteSQL(SQL.ToString) Then
                Exit Function
            End If

            Return True

        Catch ex As Exception
            Return False
        Finally
            dataAccess = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function GetPerson(ByVal PersonID As Long) As PersonInfo
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try
            Dim myData As DataTable

            GetPerson = Nothing

            With SQL

                myData = dataAccess.GetSQLDataTable("crm3_getpersondata " & PersonID)

                Try

                    If Not IsNothing(myData) Then
                        If myData.Rows.Count > 0 Then

                            Dim myPerson As New PrivateIndividual.PersonInfo
                            Dim myRow As DataRow = myData.Rows(0)

                            With myPerson
                                .Surname = IIf(IsDBNull(myRow.Item("person_lname")), "", myRow.Item("person_lname"))
                                .Firstname = IIf(IsDBNull(myRow.Item("person_fname")), "", myRow.Item("person_fname"))
                                .Title = IIf(IsDBNull(myRow.Item("person_title")), "", myRow.Item("person_title"))
                                .DestinationId = myRow.Item("person_destinationid")
                                .DestinationName = IIf(IsDBNull(myRow.Item("destination")), "", myRow.Item("destination"))
                                .DOB = IIf(IsDBNull(myRow.Item("person_dob")), Nothing, myRow.Item("person_dob"))
                                .JobTitle = IIf(IsDBNull(myRow.Item("person_jobtitle")), "", myRow.Item("person_jobtitle"))
                                .NationalityId = myRow.Item("person_natid")
                                .NationalityName = IIf(IsDBNull(myRow.Item("country_nationality")), "", myRow.Item("country_nationality"))
                                .PassportNo = IIf(IsDBNull(myRow.Item("person_passportno")), "", myRow.Item("person_passportno"))
                                .PassportExp = IIf(IsDBNull(myRow.Item("person_passportdtexpiry")), Nothing, myRow.Item("person_passportdtexpiry"))
                                .PersonId = myRow.Item("person_id")
                                .Qualification = IIf(IsDBNull(myRow.Item("person_qualification")), "", myRow.Item("person_qualification"))
                                .ResidenceId = myRow.Item("person_residentid")
                                .ResidenceName = IIf(IsDBNull(myRow.Item("resident")), "", myRow.Item("resident"))
                                .Salary = IIf(IsDBNull(myRow.Item("person_salary")), "", myRow.Item("person_salary"))
                                .UKVisaExpiry = IIf(IsDBNull(myRow.Item("ukvisaexpiry")), Nothing, myRow.Item("ukvisaexpiry"))
                                .UKVisaId = myRow.Item("person_ukvisaid")
                                .UKVisaName = IIf(IsDBNull(myRow.Item("ukvisa_name")), "", myRow.Item("ukvisa_name"))
                                .AccountManagerId = myRow.Item("person_accountmanager")
                                .WhereHeard = CommonDataFunctions.GetWhereHeard(PersonID, CommonDataFunctions.AdvertLinkType.Person)
                                .CompanyRoleId = myRow.Item("person_companyroleid")
                                .WebUserName = IIf(IsDBNull(myRow.Item("person_webusername")), "", myRow.Item("person_webusername"))
                                .WebPassword = IIf(IsDBNull(myRow.Item("person_webpassword")), "", myRow.Item("person_webpassword"))
                                .WebAccess = myRow.Item("person_webaccess")
                                .SkilledOccupationId = myRow.Item("person_skilledoccid")
                                .SkillOccupationName = IIf(IsDBNull(myRow.Item("skilledocc_title")), "", myRow.Item("skilledocc_title"))
                                .EnableLogin = myRow.Item("person_enablelogin")
                            End With

                            Return myPerson

                        End If
                    End If

                Catch ex As System.Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical)

                Finally
                    If Not IsNothing(myData) Then
                        myData.Dispose()
                    End If
                End Try

            End With
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            
        End Try
    End Function

    Public Shared Function UpdateVisaExpiryDate(ByVal PersonId As Long, ByVal VisaExpiry As Date, ByVal EventMessage As String) As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Try
            If dataAccess.ExecuteSQL("Update D_Person set Person_ExpiryDT = '" & cmn.FormatDate(VisaExpiry) & "' where Person_ID = " & PersonId) Then
                tsk.GblFunc_Add_Event("Details Changed", EventMessage, 1, PersonId, TasksEvents.EventTypes.EventPerson)
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function UpdateVisaType(ByVal PersonId As Long, ByVal VisaId As Integer, ByVal EventMessage As String) As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Try
            If dataAccess.ExecuteSQL("Update D_Person set Person_UKVisaID = " & VisaId & " Where Person_ID = " & PersonId) Then
                tsk.GblFunc_Add_Event("Details Changed", EventMessage.Trim, 1, PersonId, TasksEvents.EventTypes.EventPerson)
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function GenerateWebPassword(ByVal PersonId As Long) As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess

        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try


            Dim myData As DataTable
            Dim userName As String
            Dim firstName As String
            Dim lastName As String
            SQL = New StringBuilder

            With SQL
                .Append("Select p.person_fname,p.person_lname, p.person_webusername from D_Person P")
                .Append(" Where p.Person_ID=" & PersonId)
            End With

            myData = dataAccess.GetSQLDataTable(SQL.ToString)

            firstName = myData.Rows(0).Item(0)
            lastName = myData.Rows(0).Item(1)
            userName = IIf(IsDBNull(myData.Rows(0).Item(2)), "", myData.Rows(0).Item(2))

            SQL.Remove(0, SQL.Length)

            With SQL
                .Append("Declare @User as varchar(50) " & vbCrLf)
                .Append(" Declare @PW as varchar(50) " & vbCrLf & vbCrLf)
                .Append(" Exec SP_GenerateWebLogin @Fname = '" & cmn.SwapQuotes(firstName.Trim) & "'")
                .Append(" , @Lname = '" & cmn.SwapQuotes(lastName.Trim) & "'")
                .Append(" , @username = @User OUTPUT ")
                .Append(" , @password = @pw OUTPUT ")
                .Append(" Select @user as Username, @pw as Password")
            End With

            myData = dataAccess.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    Dim Pwd As String = myData.Rows(0).Item("password")
                    Dim User As String = myData.Rows(0).Item("username")

                    SQL = New StringBuilder

                    With SQL
                        If userName <> "" Then
                            .Append("Update D_Person set Person_WebPassword = '" & cmn.SwapQuotes(Pwd.Trim) & "' ")
                            .Append("Where Person_ID = " & PersonId)
                        Else
                            .Append("Update D_Person set Person_WebPassword = '" & cmn.SwapQuotes(Pwd.Trim) & "', ")
                            .Append("person_webusername = '" & cmn.SwapQuotes(User.Trim) & "' ")
                            .Append("Where Person_ID = " & PersonId)
                        End If
                    End With

                    Return dataAccess.ExecuteSQL(SQL.ToString)

                End If
            End If

        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function HasInvoiceAddress(ByVal PersonId As Long) As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("Select count(*) from D_Addresslink ")
                .Append(" Where Addresslink_PersonId = " & PersonId)
                .Append(" And Addresslink_Type in (2,3)")
            End With

            If dataAccess.GetSQLScalar(SQL.ToString) > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetEmailAddress(ByVal PersonId As String) As String

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("Select EmailAddress_Address from D_EMailAddress ")
                .Append("where EMailAddress_PersonID = " & PersonId & " and EMailAddress_Type = 1")
            End With

            Return dataAccess.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetPostalAddressLink(ByVal DataId As Long, ByVal AddressLinkType As AddressLinkTypes, ByVal AddressType As AddressTypes) As Long

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("SELECT Addresslink_AddressId FROM D_AddressLink")


                Select Case AddressType

                    Case AddressTypes.Company
                        .Append(" WHERE AddressLink_CompanyID = " & DataId)

                    Case AddressTypes.Person
                        .Append(" WHERE AddressLink_personID = " & DataId)

                End Select


                Select Case AddressLinkType

                    Case PrivateIndividual.AddressLinkTypes.Invoice
                        .Append(" AND Addresslink_Type in (2,3)")

                    Case PrivateIndividual.AddressLinkTypes.Correspondence
                        .Append(" AND Addresslink_Type in (1,3)")

                End Select

            End With

            Return dataAccess.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetPhoneLink(ByVal DataId As Long, ByVal PhoneType As PhoneTypes, ByVal ClientType As AddressTypes) As Long


        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
        With SQL
            .Append("SELECT PhoneNumber_ID FROM D_Phonenumber")

            Select Case ClientType
                Case AddressTypes.Person
                    .Append(" WHERE Phonenumber_PersonID = " & DataId)
                Case AddressTypes.Company
                    .Append(" WHERE Phonenumber_companyID = " & DataId)
            End Select

            Select Case PhoneType
                Case PhoneTypes.PhoneMain
                    .Append(" AND PhoneNumber_Type in (1,3)")
                Case PhoneTypes.PhoneFax
                    .Append(" AND PhoneNumber_Type in (2,3)")
            End Select

        End With

        Return dataAccess.GetSQLScalar(SQL.ToString)
         Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetEmailLink(ByVal DataId As Long) As Long

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

        With SQL
            .Append("SELECT EMailaddress_ID FROM D_Emailaddress")
            .Append(" WHERE EMailaddress_PersonID = " & DataId)
            .Append(" AND EMailaddress_Type = 1")
        End With

        Return dataAccess.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetCandidatePartnerLink(ByVal CaseId As Long) As Long

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("SELECT D_Dependants.Dependant_ChildID FROM D_Case INNER JOIN")
                .Append(" D_Dependants ON D_Case.Case_CandidateID = D_Dependants.Dependant_ParentID")
                .Append(" WHERE (D_Case.Case_ID = " & CaseId & ") And (D_Dependants.Dependant_Spouse = 1)")
            End With

            Return dataAccess.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetCandidateEmailAddress(ByVal CaseId As Long) As String

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select e.emailaddress_address from d_emailaddress e, d_case c")
                .Append(" where c.case_id = " & CaseId)
                .Append(" and c.case_candidateid = e.emailaddress_personid")
                .Append(" and e.emailaddress_type = 1")
            End With

            Return dataAccess.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return String.Empty
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetDependants(ByVal ParentId As Long) As DataTable

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_Person.Person_ID, D_Dependants.Dependant_ChildDesc,")
                .Append(" [Person_Title] + ' ' + [Person_FName] + ' ' + [Person_LName] AS Name,")
                .Append(" Case Dependant_Public when 1 then 'Public' else '' end AS Status,")
                .Append(" D_Dependants.Dependant_ID,")
                .Append(" Case D_Dependants.Dependant_Spouse When 1 then 'Partner' else '' end as Spouse")
                .Append(" FROM D_Dependants INNER JOIN D_Person ON D_Dependants.Dependant_ChildID = D_Person.Person_ID ")
                .Append(" WHERE D_Dependants.Dependant_ParentID = " & ParentId)
                .Append(" ORDER BY D_Dependants.Dependant_ID")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

        End Try

    End Function

    Public Shared Function GetDependantOf(ByVal ChildId As Long) As DataTable
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_Person.Person_ID, D_Dependants.Dependant_ParentDesc, [Person_Title] + ' ' + [Person_FName] + ' ' + [Person_LName] AS Name, Case Dependant_Public when 1 then 'Public' else '' end AS Status ")
                .Append(" FROM D_Dependants INNER JOIN D_Person ON D_Dependants.Dependant_ParentID = D_Person.Person_ID")
                .Append(" WHERE D_Dependants.Dependant_ChildID = " & ChildId)
                .Append(" ORDER BY D_Dependants.Dependant_ID")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function GetParentPerson(ByVal ChildId As Long) As ParentInfo
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            Dim myData As DataTable

            GetParentPerson = Nothing

            With SQL
                .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Name")
                .Append(" FROM D_Dependants INNER JOIN D_Person ON D_Dependants.Dependant_ParentID = D_Person.Person_ID")
                .Append(" WHERE D_Dependants.Dependant_ChildID = " & ChildId)
            End With

            myData = dataAccess.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myParent As New ParentInfo

                    myParent.ParentId = myData.Rows(0).Item("person_id")
                    myParent.ParentName = IIf(IsDBNull(myData.Rows(0).Item("name")), "", myData.Rows(0).Item("name"))

                    Return myParent
                End If
            End If

        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

        End Try
    End Function

    Public Shared Function CreateDependant(ByVal ParentId As Long, ByVal ChildId As Long, ByVal RelationshipP As String, ByVal RelationshipC As String, ByVal ConName As String, ByVal ViewPublic As Boolean, ByVal DependantName As String) As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            Dim DepId As Long

            With SQL
                .Append("Insert into D_Dependants (Dependant_ParentID, Dependant_ChildID, Dependant_EnteredBy, Dependant_Childdesc, Dependant_Parentdesc, Dependant_Public)")
                .Append(" values(" & ParentId & ", " & ChildId & ", '" & cmn.SwapQuotes(ConName.Trim) & "', '" & RelationshipC & "', '" & RelationshipP & "', " & IIf(ViewPublic, 1, 0) & ")")
            End With

            If dataAccess.ExecuteSQL(SQL.ToString) Then
                DepId = dataAccess.GetIdentity

                tsk.GblFunc_Add_Event("Dependant added", DependantName & " added as a dependant of type " & RelationshipC, TasksEvents.EventStatus.SingleEvent, ParentId, TasksEvents.EventTypes.EventPerson)
                tsk.GblFunc_Add_Event("Set as Dependant", "Set as a dependant of type " & RelationshipC & " to " & GlobalCRM.CurrentCandidateName & " (" & ParentId & ")", TasksEvents.EventStatus.SingleEvent, ChildId, TasksEvents.EventTypes.EventPerson)

                Return DepId
            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
            cmn = Nothing
            tsk = Nothing
        End Try

    End Function

    Public Shared Function SetDependantSpouse(ByVal ParentId As Long, ByVal DependantId As Long, ByVal DependantName As String) As Boolean

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim Msg As String

            Msg = "Person No. " & DependantId & " (" & DependantName & ") marked as Partner"

            With SQL
                .Append("Update D_Dependants set Dependant_Spouse = ")
                .Append(" Case Dependant_ChildID when " & DependantId & " then 1 else 0 end ")
                .Append(" Where Dependant_ParentID = " & ParentId)
            End With

            If dataAccess.ExecuteSQL(SQL.ToString) Then
                tsk.GblFunc_Add_Event("Details Changed", Msg, 0, ParentId, TasksEvents.EventTypes.EventPerson)
                Return True
            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
            cmn = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function SetDependantPublicPrivate(ByVal DependantId As Long, ByVal CurrentView As String, ByVal DependantName As String, ByVal ParentId As Long) As Boolean


        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim SQL As String
        Try

        Dim Msg As String

        If CurrentView.ToUpper = "PUBLIC" Then
            SQL = "Update D_Dependants set Dependant_Public = 0 where Dependant_ID = " & DependantId
            Msg = "Dependant " & DependantName.Trim & " made private"
        Else
            SQL = "Update D_Dependants set Dependant_Public = 1 where Dependant_ID = " & DependantId
            Msg = "Dependant " & DependantName.Trim & " made public"
        End If

        If dataAccess.ExecuteSQL(SQL.ToString) Then
                tsk.GblFunc_Add_Event("Details Changed", Msg, TasksEvents.EventStatus.SingleEvent, ParentId, TasksEvents.EventTypes.EventPerson)
            Return True
        End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function DeleteDependant(ByVal DependantId As Long, ByVal DependantName As String, ByVal ParentId As Long, ByVal ChildId As Long) As Boolean
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents

        Try
            If dataAccess.ExecuteSQL("Delete from D_Dependants where dependant_ID = " & DependantId) Then
                tsk.GblFunc_Add_Event("Dependant removed", DependantName & " removed from dependants list", TasksEvents.EventStatus.SingleEvent, ParentId, TasksEvents.EventTypes.EventPerson)
                tsk.GblFunc_Add_Event("Dependancy removed", "Dependancy to " & GlobalCRM.CurrentCandidateName & " removed", TasksEvents.EventStatus.SingleEvent, ChildId, TasksEvents.EventTypes.EventPerson)
                Return True
            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function GeneratePassword(ByVal PersonId As Long) As String
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Try
            SQL = New StringBuilder
            Dim Pwd As String
            Dim dtPerson As New DataTable


            With SQL
                .Append("Select p.person_fname,p.person_lname from D_Person P ")
                .Append(" Where p.Person_ID=" & PersonId)
            End With

            dtPerson = dataAccess.GetSQLDataTable(SQL.ToString)

            SQL.Remove(0, SQL.Length)
            GeneratePassword = String.Empty

            With SQL
                .Append("Declare @User as varchar(50) " & vbCrLf)
                .Append(" Declare @PW as varchar(50) " & vbCrLf & vbCrLf)
                .Append(" Exec SP_GenerateWebLogin @Fname = '" & dtPerson.Rows(0).Item(0) & "'")
                .Append(" , @Lname = '" & dtPerson.Rows(0).Item(1) & "'")
                .Append(" , @username = @User OUTPUT ")
                .Append(" , @password = @pw OUTPUT ")
                .Append(" Select @pw as Password")
            End With

            Pwd = dataAccess.GetSQLScalar(SQL.ToString)

            If Pwd.Trim = String.Empty Then
                MsgBox("New password not created, please speak to IT", MsgBoxStyle.Critical, "Error")
                Exit Function
            End If

            SQL = New StringBuilder

            With SQL
                .Append("Update D_Person set Person_WebPassword = '" & cmn.SwapQuotes(Pwd.Trim) & "'")
                .Append("Where Person_ID = " & PersonId)
            End With

            If dataAccess.ExecuteSQL(SQL.ToString) Then
                tsk.GblFunc_Add_Event("Web Password changed", "Web access password changed to " & Pwd, 0, PersonId, TasksEvents.EventTypes.EventPerson)
                Return Pwd
            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
            cmn = Nothing
            tsk = Nothing
        End Try
    End Function

    'Public Shared Function GetContacts(ByVal CompanyId As Long) As DataTable

    '    Dim SQL As New StringBuilder

    '    With SQL
    '        .Append("Select Person_ID, Person_Fname + ' ' + Person_LName as PersonName")
    '        .Append(" From D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID")
    '        .Append(" WHERE Person_CompanyID = " & CompanyId)
    '        .Append(" AND Role_Employee in (1,2)")
    '    End With

    '    Return dataAccess.GetSQLDataTable(SQL.ToString)

    'End Function

    Public Shared Function GetPersonEvents(ByVal PersonId As Long) As DataTable
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder

        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT,")
                .Append("D_Event.Event_Description, L_Consultant.con_name, CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),")
                .Append("Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category,")
                .Append("Case When Event_LinkType = 1 then 'Case'  when Event_LinkType = 2 then 'Client' end as LinkFor")
                .Append(" FROM D_Event LEFT OUTER JOIN L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")
                .Append(" WHERE (D_Event.Event_LinkType = 2) And (D_Event.Event_LinkID = " & PersonId & ")")
                .Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc ")

            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
          
        End Try
    End Function

    Public Shared Function GetPersonCases(ByVal PersonID As Long) As DataTable

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder

        Try
            SQL = New StringBuilder

            With SQL
                .Append("Select c.Case_ID as 'ID', prod.Product_Name as 'Product', cs.CaseStage_Title as 'Status', per.Person_FName + ' ' + per.Person_LName as 'Candidate' ")
                .Append("From D_Case c ")
                .Append("Inner Join L_Products prod On c.Case_ProductID = prod.Product_ID ")
                .Append("Inner Join D_Person per On c.Case_CandidateID = per.Person_ID ")
                .Append("Inner Join L_CaseStages cs On c.Case_StatusID = CaseStage_Number ")
                .Append("Where per.Person_ID = " & PersonID)

            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing

        End Try
    End Function


    Public Shared Function GetPhoneNumbersSQL(ByVal PersonID As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("select phonenumber_id,phonenumber_name,phonenumber_number,case phonenumber_type  when 1 then 'Main' when 2 then 'Fax' when 3 then 'Both' else '' end AS Main ")
                .Append("from d_phonenumber where phonenumber_personid = " & PersonID)
                .Append(" Order By PhoneNumber_Name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function
    Public Shared Function GetEmailAddressSQL(ByVal PersonID As Long) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT EMailAddress_ID ,EMailAddress_Name , EMailAddress_Address , case EMailAddress_type when 1 then 'Main' else '' end AS Main ")
                .Append("FROM D_EmailAddress ")
                .Append("WHERE EMailAddress_PersonID = " & PersonID)
                .Append(" Order By EmailAddress_Name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function
    Public Shared Function GetPersonAddressSQL(ByVal PersonId As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL

                .Append("SELECT AddressLink_ID, AddressLink_Name, Case Addresslink_Owner When 0 then 'Co' when 1 then '' end as Owner, ")
                .Append("CASE AddressLink_Type WHEN 1 THEN 'Cor' WHEN 2 THEN 'Inv' WHEN 3 THEN 'Both' ELSE '' END as Type, ")
                .Append("left(D_Address.Address_Text,30) as FirstLine FROM D_AddressLink INNER JOIN D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID ")
                .Append("WHERE (AddressLink_PersonID = " & PersonId & ") Order By AddressLink_Name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function

    
End Class

